import json
import requests
import openpyxl

# 设置headers是为了解决服务器反爬机制
headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/103.0.0.0 Safari/537.36'}

url = 'https://api.bilibili.com/x/web-interface/wbi/search/square?limit=10&platform=web&w_rid=7deffb9117210d7768e26e0ad27436a3&wts=1683430147'

# 爬取 B站 热搜排行榜
response = requests.get(url, headers=headers)

# 将 B站 热搜排行榜信息转为 Json
context = response.text
json_data = json.loads(context)

# 获取 B站 热搜排行榜的表格数据
bilibili_top_search_list = json_data['data']['trending']['list']

# 创建 Excel 表格
excel_table = openpyxl.Workbook()
# 创建 Sheet
sheet = excel_table.create_sheet('B站热搜排行榜')

# 创建 Sheet 标题
sheet.append(['序号', '热搜内容'])

count = 1
for search_bar in bilibili_top_search_list:
    keyword = search_bar['keyword']
    sheet.append([str(count), keyword])
    count += 1

excel_table.save('B站热搜排行榜.xlsx')